﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[UDS_WorkAttendence_DutyTime] (
    [ID]			bigint			NOT NULL,
    [IDType]		nvarchar(50)	COLLATE Chinese_PRC_CI_AS NOT NULL,
    [OnDutyTime]	datetime		NOT NULL,
    [OnDutyValue]	as (dbo.fn_GetTimeValue(OnDutyTime, 0)) PERSISTED,
    [OffDutyTime]	datetime		NOT NULL,
    [OffDutyValue]	as (dbo.fn_GetTimeValue(OffDutyTime, 0)) PERSISTED,
    [IDA]			int				NOT NULL identity(1, 1),

    CONSTRAINT [PK_UDS_WorkAttendence_DutyTime] PRIMARY KEY NONCLUSTERED ([IDA] asc),
    CONSTRAINT [CK_UDS_WorkAttendence_DutyTime_ID_IDType] CHECK (([ID]=0 AND IDType=N'Company') OR ([ID]<>0 AND IDType<>N'Company')),
    CONSTRAINT [CK_UDS_WorkAttendence_DutyTime_DutyValue] CHECK (OffDutyValue > OnDutyValue)
);
CREATE UNIQUE CLUSTERED INDEX [IX_UDS_WorkAttendence_DutyTime_ID_IDType] ON dbo.UDS_WorkAttendence_DutyTime(IDType asc, [ID] asc);
GO

SET NOCOUNT ON;
INSERT INTO dbo.UDS_WorkAttendence_DutyTime ([ID], IDType, OnDutyTime, OffDutyTime ) VALUES(0, N'Company', '09:00:00', '17:00:00');
GO